﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'proc_export_ListMember')
BEGIN
    PRINT 'Dropping Procedure proc_export_ListMember'
    DROP  Procedure  proc_export_ListMember
END
GO

PRINT 'Creating Procedure proc_export_ListMember'
GO

CREATE PROCEDURE [dbo].[proc_export_ListMember]
AS
BEGIN
	SET NOCOUNT ON

	SELECT 
		u.[UserName] as [Login Name]
		,mm.[Email] as [Email]
		,m.[firstname_txt] as [First Name]
		,m.[lastname_txt] as [Last Name]
		,m.[korean_nm] as [Korean Name]
		,m.[address_txt] as [Address]
		,m.[address2_txt] as [Address Line 2]
		,m.[city_txt] as [City]
		,m.[state_cd] as [State]
		,m.[zip_cd] as [ZIP Code]
		,m.[country_nm] as [Country]
		,m.[homephone_txt] as [Home Phone]
		,m.[cellphone_txt] as [Mobile]
		,m.[member_since_dt] as [Membership Date]
		,m.[last_login_dttm] as [Last Logon Date]
		,case when m.[confirmation_guid] is null then CONVERT(bit,1) else CONVERT(bit,0) end as [Email Confirmed?]
		,m.[created_dttm] as [Online Membership Date]
		,STUFF(
                   (SELECT
                        ', ' + sg2.[group_nm]
                        FROM [dbo].[tbl_member_smallgroup] ms2 (nolock)
							inner join [dbo].[tbl_smallgroup] sg2 (nolock) on ms2.[smallgroup_id] = sg2.[smallgroup_id]
						WHERE ms2.[member_id] = m.[member_id]
                        ORDER BY sg2.[group_nm]
                        FOR XML PATH(''), TYPE
                   ).value('.','nvarchar(max)')
                   ,1,2, ''
              ) AS [Small Groups]
	FROM 
		[dbo].[tbl_member] m (NOLOCK)
		INNER JOIN [dbo].[aspnet_Users] u (NOLOCK) on m.[UserId] = u.[UserId]
		INNER JOIN [dbo].[aspnet_Membership] mm (NOLOCK) on m.[UserId] = mm.[UserId]
		LEFT JOIN [dbo].[tbl_member_smallgroup] ms (NOLOCK) on m.[member_id] = ms.[member_id]
		LEFT JOIN [dbo].[tbl_smallgroup] sg (NOLOCK) on ms.[smallgroup_id] = sg.[smallgroup_id]
	GROUP BY
		u.[UserName]
		,mm.[Email]
		,m.[firstname_txt]
		,m.[lastname_txt]
		,m.[korean_nm]
		,m.[address_txt]
		,m.[address2_txt]
		,m.[city_txt]
		,m.[state_cd]
		,m.[zip_cd]
		,m.[country_nm]
		,m.[homephone_txt]
		,m.[cellphone_txt]
		,m.[member_since_dt]
		,m.[last_login_dttm]
		,m.[confirmation_guid]
		,m.[created_dttm]
END
GO

GRANT EXEC ON dbo.proc_export_ListMember TO PUBLIC
GO

